/**
 * 
 */
package arisufascion.com.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import arisufascion.com.model.Attribute;
import arisufascion.com.model.ImageSlider;
import arisufascion.com.model.Product;
import arisufascion.com.model.Profile;

/**
 * @author Kelvin
 * 
 */
public class ProductDataSource {
	// Database fields
	private SQLiteDatabase database;
	private MySQLiteHelper dbHelper;

	private String[] allProductsColumns = { TblProduct.COLUMN_ID,
			TblProduct.COLUMN_VIRTUEMART_PRODUCT_ID,
			TblProduct.COLUMN_PRODUCT_NAME, TblProduct.COLUMN_PRODUCT_DESC,
			TblProduct.COLUMN_PRODUCT_S_DESC, TblProduct.COLUMN_PRODUCT_PRICE,
			TblProduct.COLUMN_CURRENCY_NAME, TblProduct.COLUMN_CATEGORY_NAME,
			TblProduct.COLUMN_COLOR_ID, TblProduct.COLUMN_COLOR_QUANTITY,
			TblProduct.COLUMN_COLOR_IMAGE, TblProduct.COLUMN_COLOR_SIZE,
			TblProduct.COLUMN_QUANTITY };
/*
	private String[] allProductImageColumns = { TblProductImage.COLUMN_ID,
			TblProductImage.COLUMN_FILE_URL, TblProductImage.COLUMN_PRODUCT_ID };
	
	
	private String[] allAttributeColumns = { TblColor.COLUMN_COLORID,
			TblColor.COLUMN_QUANTITY, TblColor.COLUMN_COLORNAME,
			TblColor.COLUMN_SIZEID, TblColor.COLUMN_PHOTO,TblColor.COLUMN_PRODUCT_ID };

	*/
	private String[] allProfileColumns = { TblProfile.COLUMN_ID,
			TblProfile.COLUMN_NAME, TblProfile.COLUMN_COMPANY_NAME,
			TblProfile.COLUMN_EMAIL, TblProfile.COLUMN_TITLE,
			TblProfile.COLUMN_FIRST_NAME, TblProfile.COLUMN_MIDDLE_NAME,
			TblProfile.COLUMN_LAST_NAME, TblProfile.COLUMN_PHONE_1,
			TblProfile.COLUMN_PHONE_2, TblProfile.COLUMN_FAX,
			TblProfile.COLUMN_ADDRESS_1, TblProfile.COLUMN_ADDRESS_2,
			TblProfile.COLUMN_CITY, TblProfile.COLUMN_COUNTRY_NAME,
			TblProfile.COLUMN_COUNTRY_ID, TblProfile.COLUMN_STATE_NAME,
			TblProfile.COLUMN_STATE_ID, TblProfile.COLUMN_ZIP,
			TblProfile.COLUMN_CREATED_ON, };

	public ProductDataSource(Context context) {
		dbHelper = new MySQLiteHelper(context);
	}

	public void open() throws SQLException {
		database = dbHelper.getWritableDatabase();
	}

	public void close() {
		dbHelper.close();

	}

	public boolean isOpen() {
		if (database.isOpen())
			return true;
		return false;
	}

	public boolean createImagesProduct(String file_url, int product_id) {
		database.beginTransaction();
		try {

			ContentValues cv = new ContentValues();
			cv.put(TblProductImage.COLUMN_FILE_URL, file_url);
			cv.put(TblProductImage.COLUMN_PRODUCT_ID, product_id);
			database.insert(TblProductImage.TABLE_NAME, null, cv);

			database.setTransactionSuccessful();
			return true;

		} catch (Exception ex) {
			return false;
		} finally {
			database.endTransaction();
		}
	}
	
	public boolean insertAttribute(Attribute attribute,int product_id){
		database.beginTransaction();
		try {

			ContentValues cv = new ContentValues();
			cv.put(TblColor.COLUMN_COLORID,attribute.getColorid());
			cv.put(TblColor.COLUMN_QUANTITY,attribute.getQuantity());
			cv.put(TblColor.COLUMN_COLORNAME, attribute.getColorname());
			cv.put(TblColor.COLUMN_SIZEID, attribute.getSizeid());
			cv.put(TblColor.COLUMN_PHOTO, attribute.getPhoto());
			cv.put(TblColor.COLUMN_PRODUCT_ID, product_id);
			
			database.insert(TblColor.TABLE_NAME, null, cv);

			database.setTransactionSuccessful();
			return true;

		} catch (Exception ex) {
			return false;
		} finally {
			database.endTransaction();
		}
	}

	public void InsertdatatoTable(int getVirtuemart_product_id,
			String txt_product_name, String txt_product_desc,
			String txt_product_s_desc, float txt_product_price, int color_id,
			int color_quantity, String color_image, int quantity, int color_size) {

		database.beginTransaction();
		try {
			ContentValues cv = new ContentValues();
			cv.put("virtuemart_product_id", getVirtuemart_product_id);
			cv.put("product_name", txt_product_name);
			cv.put(TblProduct.COLUMN_PRODUCT_DESC, txt_product_desc);
			cv.put(TblProduct.COLUMN_PRODUCT_S_DESC, txt_product_s_desc);
			cv.put(TblProduct.COLUMN_PRODUCT_PRICE, txt_product_price);
			cv.put(TblProduct.COLUMN_COLOR_ID, color_id);
			cv.put(TblProduct.COLUMN_COLOR_QUANTITY, color_quantity);
			cv.put(TblProduct.COLUMN_COLOR_IMAGE, color_image);
			cv.put(TblProduct.COLUMN_QUANTITY, quantity);
			cv.put(TblProduct.COLUMN_COLOR_SIZE, color_size);

			database.insert(TblProduct.TABLE_NAME, null, cv);

			database.setTransactionSuccessful();
			Log.d("SaveProduct", "informationsaved");

		} catch (Exception ex) {
			Log.d("SaveEmployee", ex.toString());
		} finally {
			database.endTransaction();
		}
	}

	public void insertProfile(Profile profile) {
		database.beginTransaction();
		try {

			ContentValues cv = new ContentValues();

			cv.put(TblProfile.COLUMN_ID, profile.getId());
			cv.put(TblProfile.COLUMN_NAME, profile.getName());
			cv.put(TblProfile.COLUMN_COMPANY_NAME, profile.getCompany());
			cv.put(TblProfile.COLUMN_EMAIL, profile.getEmail());
			cv.put(TblProfile.COLUMN_TITLE, profile.getTitle());
			cv.put(TblProfile.COLUMN_FIRST_NAME, profile.getFirst_name());
			cv.put(TblProfile.COLUMN_MIDDLE_NAME, profile.getMiddle_name());
			cv.put(TblProfile.COLUMN_LAST_NAME, profile.getLast_name());
			cv.put(TblProfile.COLUMN_PHONE_1, profile.getPhone_1());
			cv.put(TblProfile.COLUMN_PHONE_2, profile.getPhone_2());
			cv.put(TblProfile.COLUMN_FAX, profile.getFax());
			cv.put(TblProfile.COLUMN_ADDRESS_1, profile.getAddress_1());
			cv.put(TblProfile.COLUMN_ADDRESS_2, profile.getAddress_2());
			cv.put(TblProfile.COLUMN_CITY, profile.getCity());
			cv.put(TblProfile.COLUMN_COUNTRY_ID, profile.getCountry_id());
			cv.put(TblProfile.COLUMN_COUNTRY_NAME, profile.getCountry_name());
			cv.put(TblProfile.COLUMN_STATE_ID, profile.getState_id());
			cv.put(TblProfile.COLUMN_STATE_NAME, profile.getState_name());
			cv.put(TblProfile.COLUMN_ZIP, profile.getZip());
			cv.put(TblProfile.COLUMN_CREATED_ON, profile.getCreated_on());

			database.insert(TblProfile.TABLE_NAME, null, cv);

			database.setTransactionSuccessful();

			Log.d("save profile", "informationsaved");

		} catch (Exception ex) {
			Log.d("save profile", ex.toString());
		} finally {
			database.endTransaction();
		}
	}

	public Product getProduct(String productId, int color_id) {

		Product localKProduct = new Product();

		Cursor localCursor = database.rawQuery("SELECT * FROM "
				+ TblProduct.TABLE_NAME + " WHERE "
				+ TblProduct.COLUMN_VIRTUEMART_PRODUCT_ID + "=" + productId
				+ " and " + TblProduct.COLUMN_COLOR_ID + "=" + color_id, null);

		if (localCursor.moveToFirst()) {
			do {
				localKProduct = cursorToProduct(localCursor);
				localKProduct.setImages(getAllImageSliders(Integer
						.parseInt(localKProduct.getVirtuemart_product_id())));
			} while (localCursor.moveToNext());
		}
		if (localCursor != null && !localCursor.isClosed())
			localCursor.close();

		return localKProduct;
	}

	public Product getProductById(String productId) {

		Product localKProduct = new Product();

		Cursor localCursor = database.rawQuery("SELECT * FROM "
				+ TblProduct.TABLE_NAME + " WHERE "
				+ TblProduct.COLUMN_VIRTUEMART_PRODUCT_ID + "=" + productId, null);

		if (localCursor.moveToFirst()) {
			do {
				localKProduct = cursorToProduct(localCursor);
				localKProduct.setImages(getAllImageSliders(Integer
						.parseInt(localKProduct.getVirtuemart_product_id())));
			} while (localCursor.moveToNext());
		}
		if (localCursor != null && !localCursor.isClosed())
			localCursor.close();

		return localKProduct;
	}
	
	public List<Product> getProducts() {
		List<Product> products = new ArrayList<Product>();

		Cursor cursor = database.query(TblProduct.TABLE_NAME,
				allProductsColumns, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Product product = cursorToProduct(cursor);
			products.add(product);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}

		return products;
	}

	public void deleteProduct(int product_id,int color_id) {
		database.delete(TblProduct.TABLE_NAME,
				TblProduct.COLUMN_VIRTUEMART_PRODUCT_ID + "=" + product_id+ " and "+ TblColor.COLUMN_COLORID+"="+color_id,
				null);
	}

	public void deleteAllProduct() {
		database.delete(TblProduct.TABLE_NAME, null, null);
		database.delete(TblColor.TABLE_NAME, null, null);
		database.delete(TblProductImage.TABLE_NAME, null, null);

	}

	public void deletedAllProfile() {
		database.delete(TblProfile.TABLE_NAME, null, null);
	}

	private Product cursorToProduct(Cursor cursor) {
		Product product = new Product();

		product.setVirtuemart_product_id(cursor.getString(1));
		product.setProduct_name(cursor.getString(2));
		product.setProduct_desc(cursor.getString(3));
		product.setProduct_s_desc(cursor.getString(4));
		product.setProduct_price(cursor.getFloat(5));
		product.setCurrency_name(cursor.getString(6));
		product.setCategory_name(cursor.getString(7));
		product.setColor_id(cursor.getInt(8));
		product.setColor_quantity(cursor.getInt(9));
		product.setColor_image(cursor.getString(10));

		product.setColor_size(cursor.getInt(11));
		product.setQuantity(cursor.getInt(12));

		// cursor.close();
		

		return product;
	}

	public List<Profile> getProfiles() {
		List<Profile> profiles = new ArrayList<Profile>();

		Cursor cursor = database.query(TblProfile.TABLE_NAME,
				allProfileColumns, null, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			Profile profile = cursorToProfile(cursor);
			profiles.add(profile);
			cursor.moveToNext();
		}
		// Make sure to close the cursor
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}

		return profiles;
	}

	private Profile cursorToProfile(Cursor cursor) {
		Profile pf = new Profile();
		pf.setId(cursor.getInt(0));
		pf.setName(cursor.getString(1));
		pf.setCompany_name(cursor.getString(2));
		pf.setEmail(cursor.getString(3));

		pf.setTitle(cursor.getString(4));
		pf.setFirst_name(cursor.getString(5));
		pf.setMiddle_name(cursor.getString(6));
		pf.setLast_name(cursor.getString(7));

		pf.setPhone_1(cursor.getString(8));
		pf.setPhone_2(cursor.getString(9));
		pf.setFax(cursor.getString(10));
		pf.setAddress_1(cursor.getString(11));
		pf.setAddress_2(cursor.getString(12));
		pf.setCity(cursor.getString(13));
		pf.setCountry_name(cursor.getString(14));
		pf.setCountry_id(cursor.getString(15));
		pf.setState_name(cursor.getString(16));
		pf.setState_id(cursor.getString(17));
		pf.setZip(cursor.getString(18));
		pf.setCreated_on(cursor.getString(18));

		// cursor.close();
		return pf;
	}

	public int updateProfile(Profile profile) {
		ContentValues cv = new ContentValues();
		cv.put(TblProfile.COLUMN_NAME, profile.getName());
		cv.put(TblProfile.COLUMN_COMPANY_NAME, profile.getCompany_name());
		cv.put(TblProfile.COLUMN_EMAIL, profile.getEmail());
		cv.put(TblProfile.COLUMN_PHONE_1, profile.getPhone_1());
		cv.put(TblProfile.COLUMN_ADDRESS_1, profile.getAddress_1());
		return database.update(TblProfile.TABLE_NAME, cv, null, null);
	}

	public int updateProduct(Product p) {
		ContentValues args = new ContentValues();
		args.put("quantity", p.getQuantity());
		if(p.getAttribute()!=null){
			return database.update(
					TblProduct.TABLE_NAME,
					args,
					TblProduct.COLUMN_VIRTUEMART_PRODUCT_ID + "="
							+ p.getVirtuemart_product_id()+" and "+TblColor.COLUMN_COLORID+"="+p.getColor_id(), null);
		}else{
			return database.update(
					TblProduct.TABLE_NAME,
					args,
					TblProduct.COLUMN_VIRTUEMART_PRODUCT_ID + "="
							+ p.getVirtuemart_product_id(), null);
		}
	
	}

	public ArrayList<ImageSlider> getAllImageSliders(int pro_id) {
		ArrayList<ImageSlider> arr = new ArrayList<ImageSlider>();
		String query = "Select * from " + TblProductImage.TABLE_NAME
				+ " where " + TblProductImage.COLUMN_PRODUCT_ID + "=" + pro_id;
		Cursor cursor = database.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			do {
				ImageSlider imgSlider = getImageSliderFromCursor(cursor);
				arr.add(imgSlider);
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed())
			cursor.close();
		return arr;
	}
	public ArrayList<Attribute> getAllAttributes(int product_id){
		ArrayList<Attribute> arr = new ArrayList<Attribute>();
		String query = "Select * from " + TblColor.TABLE_NAME
				+ " where " + TblColor.COLUMN_PRODUCT_ID + "=" + product_id;
		Cursor cursor = database.rawQuery(query, null);
		if (cursor.moveToFirst()) {
			do {
				Attribute attribute = getAttributeFromCursor(cursor);
				arr.add(attribute);
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed())
			cursor.close();
		return arr;
	}

	public ImageSlider getImageSliderFromCursor(Cursor cursor) {
		ImageSlider img = new ImageSlider();
		img.setFile_url(cursor.getString(1));
		img.setProduct_id(cursor.getInt(2));
		return img;
	}
public Attribute getAttributeFromCursor(Cursor cursor){
	Attribute attribute=new Attribute();
	attribute.setColorid(cursor.getInt(0));
	attribute.setQuantity(cursor.getInt(1));
	attribute.setColorname(cursor.getString(2));
	attribute.setSizeid(cursor.getInt(3));
	attribute.setPhoto(cursor.getString(4));
	attribute.setProduct_id(cursor.getInt(5));
	
	return attribute;
}
}
